-- http://www.phpmyadmin.net
--




--
-- Database: `mig_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `auction`
--

CREATE TABLE IF NOT EXISTS `auction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by_profile_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `stock_id` int(11) NOT NULL,
  `sold_to_profile_id` int(11) DEFAULT NULL,
  `sold_to_company_id` int(11) DEFAULT NULL,
  `auction_bid_id` int(11) DEFAULT NULL,
  `bid_price` decimal(11,2) NOT NULL,
  `reserve_price` decimal(11,2) NOT NULL,
  `bid_increment` decimal(11,2) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `number_of_bids` int(11) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_DEE4F593340BCA85` (`auction_bid_id`),
  KEY `IDX_DEE4F593FFA30A58` (`created_by_profile_id`),
  KEY `IDX_DEE4F593979B1AD6` (`company_id`),
  KEY `IDX_DEE4F593DCD6110` (`stock_id`),
  KEY `IDX_DEE4F5933291555F` (`sold_to_profile_id`),
  KEY `IDX_DEE4F59369F05D31` (`sold_to_company_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `auction_autobid`
--

CREATE TABLE IF NOT EXISTS `auction_autobid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auction_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `profile_id` int(11) NOT NULL,
  `amount` decimal(11,2) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_69F7105B57B8F0DE` (`auction_id`),
  KEY `IDX_69F7105B979B1AD6` (`company_id`),
  KEY `IDX_69F7105BCCFA12B8` (`profile_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `auction_bid`
--

CREATE TABLE IF NOT EXISTS `auction_bid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auction_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `profile_id` int(11) NOT NULL,
  `auction_autobid_id` int(11) DEFAULT NULL,
  `amount` decimal(11,2) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_401A9C4357B8F0DE` (`auction_id`),
  KEY `IDX_401A9C43979B1AD6` (`company_id`),
  KEY `IDX_401A9C43CCFA12B8` (`profile_id`),
  KEY `IDX_401A9C436FB371E6` (`auction_autobid_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=18 ;

-- --------------------------------------------------------

--
-- Table structure for table `auction_increment`
--

CREATE TABLE IF NOT EXISTS `auction_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from` int(10) unsigned NOT NULL,
  `to` int(10) unsigned NOT NULL,
  `amount` decimal(11,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `auction_increment`
--

INSERT INTO `auction_increment` (`id`, `from`, `to`, `amount`) VALUES
(1, 0, 50000, '500.00'),
(2, 50000, 150000, '1000.00'),
(3, 150000, 300000, '1500.00'),
(4, 300000, 16000000, '2000.00');


CREATE TABLE `auction_question` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auction_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `profile_id` int(11) NOT NULL,
  `auction_reply_id` int(11) DEFAULT NULL,
  `question` longtext COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_37F97A057B8F0DE` (`auction_id`),
  KEY `IDX_37F97A0979B1AD6` (`company_id`),
  KEY `IDX_37F97A0CCFA12B8` (`profile_id`),
  KEY `IDX_37F97A03F869091` (`auction_reply_id`),
  CONSTRAINT `FK_37F97A03F869091` FOREIGN KEY (`auction_reply_id`) REFERENCES `auction_reply` (`id`),
  CONSTRAINT `FK_37F97A057B8F0DE` FOREIGN KEY (`auction_id`) REFERENCES `auction` (`id`),
  CONSTRAINT `FK_37F97A0979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK_37F97A0CCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `auction_reply` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auction_question_id` int(11) NOT NULL,
  `profile_id` int(11) NOT NULL,
  `reply` longtext COLLATE utf8_unicode_ci NOT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_B2BC5FE94183E823` (`auction_question_id`),
  KEY `IDX_B2BC5FE9CCFA12B8` (`profile_id`),
  CONSTRAINT `FK_B2BC5FE94183E823` FOREIGN KEY (`auction_question_id`) REFERENCES `auction_question` (`id`),
  CONSTRAINT `FK_B2BC5FE9CCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `auction_public_holiday` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `holiday` date NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `dispute` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by_profile_id` int(11) NOT NULL,
  `from_company_id` int(11) NOT NULL,
  `against_company_id` int(11) NOT NULL,
  `auction_id` int(11) NOT NULL,
  `complaint` longtext COLLATE utf8_unicode_ci NOT NULL,
  `resolution` longtext COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_3C925007FFA30A58` (`created_by_profile_id`),
  KEY `IDX_3C925007BC3FDCD1` (`from_company_id`),
  KEY `IDX_3C92500767F3FF09` (`against_company_id`),
  KEY `IDX_3C92500757B8F0DE` (`auction_id`),
  CONSTRAINT `FK_3C92500757B8F0DE` FOREIGN KEY (`auction_id`) REFERENCES `auction` (`id`),
  CONSTRAINT `FK_3C92500767F3FF09` FOREIGN KEY (`against_company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK_3C925007BC3FDCD1` FOREIGN KEY (`from_company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK_3C925007FFA30A58` FOREIGN KEY (`created_by_profile_id`) REFERENCES `profile` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




CREATE TABLE `auction_basket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auction_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `profile_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL,
  `archived` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_5BD5FC4657B8F0DE` (`auction_id`),
  KEY `IDX_5BD5FC46979B1AD6` (`company_id`),
  KEY `IDX_5BD5FC46CCFA12B8` (`profile_id`),
  CONSTRAINT `FK_5BD5FC46CCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`),
  CONSTRAINT `FK_5BD5FC4657B8F0DE` FOREIGN KEY (`auction_id`) REFERENCES `auction` (`id`),
  CONSTRAINT `FK_5BD5FC46979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




--
-- Constraints for dumped tables
--

--
-- Constraints for table `auction`
--
ALTER TABLE `auction`
  ADD CONSTRAINT `FK_DEE4F5933291555F` FOREIGN KEY (`sold_to_profile_id`) REFERENCES `profile` (`id`),
  ADD CONSTRAINT `FK_DEE4F593340BCA85` FOREIGN KEY (`auction_bid_id`) REFERENCES `auction_bid` (`id`),
  ADD CONSTRAINT `FK_DEE4F59369F05D31` FOREIGN KEY (`sold_to_company_id`) REFERENCES `company` (`id`),
  ADD CONSTRAINT `FK_DEE4F593979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  ADD CONSTRAINT `FK_DEE4F593DCD6110` FOREIGN KEY (`stock_id`) REFERENCES `stock` (`id`),
  ADD CONSTRAINT `FK_DEE4F593FFA30A58` FOREIGN KEY (`created_by_profile_id`) REFERENCES `profile` (`id`);

--
-- Constraints for table `auction_autobid`
--
ALTER TABLE `auction_autobid`
  ADD CONSTRAINT `FK_69F7105B57B8F0DE` FOREIGN KEY (`auction_id`) REFERENCES `auction` (`id`),
  ADD CONSTRAINT `FK_69F7105B979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  ADD CONSTRAINT `FK_69F7105BCCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`);

--
-- Constraints for table `auction_bid`
--
ALTER TABLE `auction_bid`
  ADD CONSTRAINT `FK_401A9C4357B8F0DE` FOREIGN KEY (`auction_id`) REFERENCES `auction` (`id`),
  ADD CONSTRAINT `FK_401A9C436FB371E6` FOREIGN KEY (`auction_autobid_id`) REFERENCES `auction_autobid` (`id`),
  ADD CONSTRAINT `FK_401A9C43979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  ADD CONSTRAINT `FK_401A9C43CCFA12B8` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`);




ALTER TABLE `mig_db`.`stock` ADD COLUMN `auction_id` INT(11) NULL DEFAULT NULL  AFTER `price_guide_id`, ADD COLUMN `times_listed` INT NOT NULL AFTER `loaded_on_priceguide`, ADD COLUMN `highest_bid` DECIMAL(11,2) NOT NULL AFTER `times_listed`;

ALTER TABLE `mig_db`.`stock` 
  ADD CONSTRAINT `FK_stockauction`
  FOREIGN KEY (`auction_id` )
  REFERENCES `mig_db`.`auction` (`id` )
  ON DELETE RESTRICT
  ON UPDATE RESTRICT
, ADD INDEX `IDX_stockauction` (`auction_id`) ;


ALTER TABLE app_config add column `auction_max_days` smallint(6) DEFAULT NULL after price_guide_completion_days;
UPDATE app_config set auction_max_days=7;

ALTER TABLE `mig_db`.`price_guide_club_member` CHANGE COLUMN `price_guide_allowed_member_id` `price_guide_allowed_member_id` INT(11) NULL DEFAULT NULL;

ALTER TABLE `profile_permissions`  ADD COLUMN `auction_page` tinyint(1) NOT NULL AFTER `pg_un_archive`, ADD COLUMN `auction_create` tinyint(1) NOT NULL AFTER `auction_page`, ADD COLUMN `auction_bid` tinyint(1) NOT NULL AFTER `auction_create`;
ALTER TABLE `profile_permissions` ADD COLUMN `stock_route_auction` tinyint(1) NOT NULL AFTER `stock_view`;
alter table profile_permissions add column admin_auction tinyint(1) NOT NULL after admin_user;

ALTER TABLE `profile_permissions_override`  ADD COLUMN `auction_page` tinyint(1) NOT NULL AFTER `pg_un_archive`, ADD COLUMN `auction_create` tinyint(1) NOT NULL AFTER `auction_page`, ADD COLUMN `auction_bid` tinyint(1) NOT NULL AFTER `auction_create`;
ALTER TABLE `profile_permissions_override` ADD COLUMN `stock_route_auction` tinyint(1) NOT NULL AFTER `stock_view`;
alter table profile_permissions_override add column admin_auction tinyint(1) NOT NULL after admin_user;

ALTER TABLE `mig_db`.`auction_increment` CHANGE COLUMN `from` `range_from` INT(10) UNSIGNED NOT NULL  , CHANGE COLUMN `to` `range_to` INT(10) UNSIGNED NOT NULL  ;


update company set price_guide=0;
update company set price_guide=1 where company_group_id in(1,21);



